package dao.impl;

import core.BaseDao;
import dao.MemberDao;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import pojo.Member;
import pojo.MemberRole;
import util.DruidUtil;
import util.MyMapListHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MemberDaoImpl extends BaseDao implements MemberDao {

    protected Connection connection;

    public MemberDaoImpl() {
        this.connection= DruidUtil.getConnection();
    }

    public void setConnection(Connection connection){
        this.connection=connection;
    }
    public Connection getConnection() {
        return connection;
    }

    /**
     * 查询所有会员
     * @param member
     * @return
     */
    @Override
    public List<Member> listMember(Member member) {
        try {
            sql = "SELECT * FROM member,member_role WHERE member.member_role_code = member_role.member_role_code";
            List<String> params = new ArrayList<>();
            if ("-1".equals(member.getMemberStatus()) || member.getMemberStatus() == null){
                sql += " AND member_status!=0";
            }else {
                sql += " AND member_status = ?";
                params.add(member.getMemberStatus());
            }
            if (member.getMemberName() != null && !member.getMemberName().isEmpty()){
                sql += " AND member_name LIKE ?";
                params.add("%"+member.getMemberName()+"%");
            }
            if (member.getMemberPhone() != null && !member.getMemberPhone().isEmpty()){
                sql += " AND member_phone LIKE ?";
                params.add("%"+member.getMemberPhone()+"%");
            }
            if ( member.getMemberRoleCode()!=null && !"0".equals(member.getMemberRoleCode())){
                sql += " AND member.member_role_code = ?";
                params.add(member.getMemberRoleCode());
            }

            sql += " ORDER BY member_id DESC LIMIT ?,?";

            Integer page = (member.getPage()-1)*member.getLimit();
            Integer limit = member.getLimit();
            List<Map<String, Object>> queryList;
            if (params.size() == 2){
                queryList = queryRunner.query(sql, new MyMapListHandler(), params.get(0), params.get(1), page, limit);
                return getMembers(queryList);
            }else if (params.size() == 1){
                queryList = queryRunner.query(sql, new MyMapListHandler(), params.get(0),page, limit);
                return getMembers(queryList);
            }else if (params.size() == 3){
                queryList = queryRunner.query(sql, new MyMapListHandler(), params.get(0), params.get(1), params.get(2), page, limit);
                return getMembers(queryList);
            }else if (params.size() == 4){
                queryList = queryRunner.query(sql, new MyMapListHandler(), params.get(0), params.get(1), params.get(2), params.get(3), page, limit);
                return getMembers(queryList);
            }
            else {
                queryList = queryRunner.query(sql, new MyMapListHandler(), page, limit);
                return getMembers(queryList);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }

        return new ArrayList<>();
    }
    private List<Member> getMembers(List<Map<String, Object>> queryList) throws IllegalAccessException, InvocationTargetException {
        List<Member> memberList = new ArrayList<>();
        for (Map<String, Object> map : queryList) {
            Member member1 = new Member();
            MemberRole memberRole = new MemberRole();
            BeanUtils.populate(member1, map);
            BeanUtils.populate(memberRole, map);
            member1.setMemberRole(memberRole);
            memberList.add(member1);
        }
        return memberList;
    }

    /**
     * 查询对应积分的会员类型
     * @param memberCount
     * @return
     */
    @Override
    public MemberRole findRoleByCount(String memberCount) {
        try {
            sql = "SELECT * FROM member_role WHERE  member_role_money_min<=?  AND ?<=member_role_money_max";
            return queryRunner.query(sql, new BeanHandler<>(MemberRole.class, rowProcessor), memberCount, memberCount);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new MemberRole();
    }

    /**
     * 扣除会员余额
     * @param memberId
     * @param payMoney
     * @return
     */
    @Override
    public Integer subMoney(String memberId, String payMoney) {
        try {
            sql = "UPDATE member SET member_money=member_money-? WHERE member_id=?";
            return queryRunner.update(sql, payMoney, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    /**
     * 根据会员id修改密码
     * @param memberId
     * @param newPassword
     * @return
     */
    @Override
    public Integer updateMemberPasswordByMemberId(String memberId,String newPassword) {
        try {
            sql = "UPDATE member SET member_password = ?,member_update_time=? WHERE member_id = ?";
            return queryRunner.update(sql,newPassword,new Timestamp(System.currentTimeMillis()),memberId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    /**
     * 更新会员类型
     * @param memberId
     * @param memberRoleCode
     * @return
     */
    @Override
    public Integer updateMemberRole(String memberId, String memberRoleCode) {
        try {
            sql = "UPDATE member SET member_role_code=? WHERE member_id=?";
            queryRunner.update(sql, memberRoleCode, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 增加会员积分
     * @param memberId
     * @param orderMoney
     * @return
     */
    @Override
    public Integer addMemberCount(String memberId, String orderMoney) {
        try {
            sql = "UPDATE member SET member_count=member_count+? WHERE member_id=?";
            return queryRunner.update(sql, orderMoney, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 恢复会员
     * @param memberId
     * @return
     */
    @Override
    public Integer restore(Integer memberId) {
        try {
            //String sql = "UPDATE member SET member_status=1 AND member_update_time=? WHERE member_id=?";
            String sql = "UPDATE member SET member_status=1 , member_update_time=? WHERE member_id=?";
            return queryRunner.update(sql, new Timestamp(System.currentTimeMillis()),memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }


    /**
     * 真删除
     * @param id
     * @return
     */
    @Override
    public Integer realDelete(String id) {
        try {
            sql = "DELETE FROM member WHERE member_id = ?";
            return queryRunner.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 修改会员信息
     * @param member
     * @return
     */
    @Override
    public Integer edit(Member member) {
        try {
            sql = "UPDATE member SET member_phone=?,member_name=?,member_status=?,member_update_time=?";
            if (member.getMemberPassword()==null){
                sql += " WHERE member_id = ?";
                return queryRunner.update(sql, member.getMemberPhone(),
                        member.getMemberName(),
                        member.getMemberStatus(),
                        member.getMemberUpdateTime(),
                        member.getMemberId());
            }else {
                sql += ",member_password=? WHERE member_id=?";
                return queryRunner.update(sql, member.getMemberPhone(),
                        member.getMemberName(),
                        member.getMemberStatus(),
                        member.getMemberUpdateTime(),
                        member.getMemberPassword(),
                        member.getMemberId());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 检查电话号码是否存在
     * @param memberPhone
     * @return
     */
    @Override
    public Boolean checkPhoneExist(String memberPhone) {
        try {
            sql = "SELECT count(1) FROM member WHERE member_phone = ?";
            Integer count = Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), memberPhone).toString());
            if (count !=0 ){
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通过id查找会员
     * @param memberId
     * @return
     */
    @Override
    public Member findMemberById(String memberId) {
        try {
            sql = "SELECT * FROM member WHERE member_id = ?";
            return queryRunner.query(sql, new BeanHandler<>(Member.class, rowProcessor), memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Member();
    }

    /**
     * 判断会员的类型编号
     * @param money
     * @return
     */
    @Override
    public String findCode(Double money) {
        try {
            sql = "SELECT * FROM member_role WHERE ?>member_role_money_min AND ?<member_role_money_max";
            MemberRole query = queryRunner.query(sql, new BeanHandler<>(MemberRole.class, rowProcessor), money, money);
            return query.getMemberRoleCode().toString();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "0";
    }


    /**
     * 更新会员类型
     * @param memberId
     * @param memberRoleCode
     */
    @Override
    public Integer updateMemberRoleCode(String memberId, String memberRoleCode) {
        try {
            sql = "UPDATE member SET member_role_code = ? WHERE member_id = ?";
            return queryRunner.update(sql, memberRoleCode, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 获取所有会员类型名称
     * @return
     */
    @Override
    public Map<String, String> getAllMemberRoleName() {
        List<MemberRole> allMemberRole = getAllMemberRole();
        Map<String, String> memberRoleName = new HashMap<>();
        for (MemberRole memberRole : allMemberRole) {
            memberRoleName.put(memberRole.getMemberRoleCode().toString(), memberRole.getMemberRoleName());
        }
        return memberRoleName;
    }

    /**
     * 获取会员类型
     * @return
     */
    @Override
    public List<MemberRole> getAllMemberRole() {
        try {
            sql = "SELECT * FROM member_role";
            List<Map<String, Object>> queryList = queryRunner.query(sql, new MyMapListHandler());
            List<MemberRole> memberRoleList = new ArrayList<>();
            for (Map<String, Object> map : queryList) {
                MemberRole memberRole = new MemberRole();
                BeanUtils.populate(memberRole, map);
                memberRoleList.add(memberRole);
            }
            return memberRoleList;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 软删除
     * @param memberId
     * @return
     */
    @Override
    public Integer delete(String memberId) {
        try {
            sql = "UPDATE member SET member_status=0,member_update_time=? WHERE member_id = ?";
            return queryRunner.update(sql, new Timestamp(System.currentTimeMillis()), memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询所有会员 计数
     * @param member
     * @return
     */
    @Override
    public Integer countListMember(Member member) {
        try {
            sql = "SELECT count(1) FROM member,member_role WHERE member.member_role_code = member_role.member_role_code";
            List<String> params = new ArrayList<>();
            if ("-1".equals(member.getMemberStatus()) || member.getMemberStatus() == null){
                sql += " AND member_status!=0";
            }else {
                sql += " AND member_status = ?";
                params.add(member.getMemberStatus());
            }
            if (member.getMemberName() != null && !member.getMemberName().isEmpty()){
                sql += " AND member_name LIKE ?";
                params.add("%"+member.getMemberName()+"%");
            }
            if (member.getMemberPhone() != null && !member.getMemberPhone().isEmpty()){
                sql += " AND member_phone LIKE ?";
                params.add("%"+member.getMemberPhone()+"%");
            }
            if ( member.getMemberRoleCode()!=null && !"0".equals(member.getMemberRoleCode())){
                sql += " AND member.member_role_code = ?";
                params.add(member.getMemberRoleCode());
            }

            if (params.size() == 2){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), params.get(0), params.get(1)).toString());
            }else if (params.size() == 1){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), params.get(0)).toString());
            }else if (params.size() == 3){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), params.get(0), params.get(1), params.get(2)).toString());
            }
            else if (params.size() == 4){
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>(), params.get(0), params.get(1), params.get(2), params.get(3)).toString());
            }
            else {
                return Integer.valueOf(queryRunner.query(sql, new ScalarHandler<>()).toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 充值
     * @param rechargeMoney
     * @param member_id
     * @return
     */
    @Override
    public Integer recharge(Double rechargeMoney, String member_id) {
        try {
            sql = "UPDATE member SET member_money= ? WHERE member_id=?";
            return queryRunner.update(sql, rechargeMoney, member_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 通过条件查找会员
     * @param member
     * @return
     */
    @Override
    public Member findMember(Member member) {
        try {
            List<Member> memberList = new ArrayList<>();
            sql = "SELECT * FROM member m, member_role mr WHERE member_phone = ? AND m.member_role_code = mr.member_role_code";
            List<Map<String, Object>> queryList = queryRunner.query(sql, new MyMapListHandler(), member.getMemberPhone());
            for (Map<String, Object> map : queryList) {
                Member member1 = new Member();
                MemberRole memberRole = new MemberRole();
                BeanUtils.populate(member1, map);
                BeanUtils.populate(memberRole, map);
                member1.setMemberRole(memberRole);
                memberList.add(member1);
            }
            if (memberList.size()!=0){
                return memberList.get(0);
            }
           return new Member();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return new Member();
    }

    /**
     * 注册用户
     * @param member
     * @return
     */
    @Override
    public Integer memberRegister(Member member) {
        try {
            sql = "INSERT INTO member(member_name,member_phone,member_password,member_create_time,member_update_time) VALUES (?,?,?,?,?)";
            return queryRunner.update(sql, member.getMemberName(), member.getMemberPhone(), member.getMemberPassword(), member.getMemberCreateTime(), member.getMemberUpdateTime());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 增加会员积分
     * @param memberId
     * @param orderMoney
     * @param connection
     * @return
     */
    public Integer addMemberCount(String memberId, String orderMoney, Connection connection) {
        try {
            sql = "UPDATE member SET member_count=member_count+? WHERE member_id=?";
            return queryRunner.update(connection, sql, orderMoney, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 更新会员类型
     * @param memberId
     * @param memberRoleCode
     * @param connection
     * @return
     */
    public Integer updateMemberRoleCode(String memberId, String memberRoleCode, Connection connection) {
        try {
            sql = "UPDATE member SET member_role_code = ? WHERE member_id = ?";
            return queryRunner.update(connection, sql, memberRoleCode, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;

    }

    /**
     *
     * 扣除会员余额
     * @param memberId
     * @param payMoney
     * @param connection
     * @return
     */
    public Integer subMoney(String memberId, String payMoney, Connection connection) {
        try {
            sql = "UPDATE member SET member_money=member_money-? WHERE member_id=?";
            return queryRunner.update(connection, sql, payMoney, memberId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
